package com.codrim.cs.data.server.dao.mapper;

import com.codrim.cs.data.rpc.dto.param.AdvertiserReportParamDTO;
import com.codrim.cs.data.rpc.dto.param.AdvertiserWholeDayDataForPeriodParamDTO;
import com.codrim.cs.data.rpc.dto.param.AffiliateReportParamDTO;
import com.codrim.cs.data.rpc.dto.param.OfferReportField;
import com.codrim.cs.data.server.dao.entity.DailyDataRecordDO;
import com.codrim.cs.data.server.dao.entity.MergeDataRecordDO;
import com.codrim.cs.data.server.dao.param.AffAllGroupByDayAndSymbolParam;
import com.codrim.cs.data.server.dao.param.AffAllParam;
import com.codrim.cs.data.server.dao.param.AffDataRoleParam;
import com.codrim.cs.data.server.dao.param.DataRecordParam;
import com.codrim.cs.data.server.dao.param.ReportParam;
import com.codrim.cs.data.server.dao.param.SelectColumnNameAndAlias;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Arrays;
import java.util.List;

/**
 * Mybatis mapper for {@link com.codrim.cs.data.server.dao.entity.DataRecordDO} Created by Liang.Zhuge on 26/07/2017.
 */
@Mapper
public interface DataRecordMapper {

    @Select("select " +
            "sdate," +
            "symbol,"+
            "advertiser_id companyId, " +
            "clicks," +
            "unique_clicks  uniqueClicks," +
            "gross_conversions  grossConversions," +
            "revenue," +
            "cost," +
            "conversions," +
            "sc " +
            "from tb_data_record " +
            "where advertiser_id = #{advertiserId} and sdate = #{date} ")
    List<DailyDataRecordDO> advertiserDailyData(@Param("advertiserId") String advertiserId,
            @Param("date") String sdate);

    @Select("<script>" +
            "select " +
            "sdate," +
            "symbol,"+
            "advertiser_id companyId, " +
            "clicks," +
            "unique_clicks  uniqueClicks," +
            "gross_conversions  grossConversions," +
            "revenue," +
            "cost," +
            "conversions," +
            "sc " +
            "from tb_data_record " +
            "where advertiser_id = #{param.companyId} and sdate = #{date}"+
            "<choose>" +
            "<when test='param.advertiserManagerIdListSelected != null and param.advertiserManagerIdListSelected.size()!=0 and param.affiliateManagerIdListSelected != null and param.affiliateManagerIdListSelected.size()!=0'>" +
            " and (adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.affiliateManagerIdListSelected' open='(' separator=',' close=')'> #{item}</foreach>" +
            " or adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advertiserManagerIdListSelected' open='(' separator=',' close=')'> #{item}" +
            "</foreach>" +
            ")</when>" +
            "<otherwise>" +
            "<when test='param.affiliateManagerIdListSelected != null and param.affiliateManagerIdListSelected.size()!=0'>" +
            " and adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.affiliateManagerIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "<when test='param.advertiserManagerIdListSelected != null and param.advertiserManagerIdListSelected.size()!=0'>" +
            " and adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advertiserManagerIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "</otherwise> " +
            "</choose>" +
            "<if test='param.hasAllPermission==false'>"+
                "  and (1>2 "+
                    "<if test='param.advertiserManagerId != null'>"+
                    "   or adv_advertiser_manager_id = #{param.advertiserManagerId} "+
                    "</if>"+
                    "<if test='param.affiliateManagerId != null'>"+
                    "   or adv_affiliate_manager_id = #{param.affiliateManagerId} "+
                    "</if>"+
                    "<if test='param.advertiserManagerDepartmentIdSet != null and param.advertiserManagerDepartmentIdSet.size()!=0' >" +
                    "    or adv_advertiser_manager_department_id in " +
                    "    <foreach item='item' index='index' collection='param.advertiserManagerDepartmentIdSet' open='(' separator=',' close=')'>" +
                    "       #{item}" +
                    "    </foreach>" +
                    "</if>" +
                    "<if test='param.affiliateManagerDepartmentIdSet != null and param.affiliateManagerDepartmentIdSet.size()!=0' >" +
                    "    or adv_affiliate_manager_department_id in " +
                    "    <foreach item='item' index='index' collection='param.affiliateManagerDepartmentIdSet' open='(' separator=',' close=')'>" +
                    "       #{item}" +
                    "    </foreach>" +
                    "</if>" +
                " )" +
            "</if> "+
            "</script>")
    List<DailyDataRecordDO> findDataRecordForDashboard(@Param("param")DataRecordParam param,
                                                       @Param("date") String sdate);


    @Select("select " +
        "sdate," +
        "symbol,"+
        "affiliate_id companyId,"+
        "clicks," +
        "unique_clicks  uniqueClicks," +
        "gross_conversions  grossConversions," +
        "revenue," +
        "cost," +
        "conversions," +
        "sc " +
        "from tb_data_record " +
        "WHERE sdate = #{date} AND affiliate_id = #{companyId} ")
    List<DailyDataRecordDO> affiliateDailyData(@Param("companyId") String companyId, @Param("date") String sdate);

    @Select("<script>"
            + "select "
            + "sdate, "
            + "symbol, "
            + "affiliate_id companyId, "
            + "sum(clicks) clicks, "
            + "sum(unique_clicks) uniqueClicks, "
            + "sum(gross_conversions) grossConversions, "
            + "sum(revenue) revenue, "
            + "sum(cost) cost, "
            + "sum(conversions) conversions, "
            + "sum(sc) sc "
            + " from tb_data_record "
            + " where sdate = #{date} "
            + " and affiliate_id = #{param.companyId} "
            + "<if test='param.departmentIds != null'>"
            + " and aff_advertiser_manager_department_id in "
            + "    <foreach item='paramDeptId' index='index' collection='param.departmentIds' open='(' separator=',' close=')'>"
            + "       #{paramDeptId}"
            + "    </foreach> "
            + "</if>"
            + "<if test='param.advertiserManagerId != null'>"
            + " and aff_advertiser_manager_id = #{param.advertiserManagerId} "
            + "</if>"
            /* 权限 */
            + "<if test='roleParam != null'>"
            + " and (1=1 "
            + "<if test='roleParam.advertiserManagerDepartmentIds != null and roleParam.advertiserManagerDepartmentIds.size() > 0'>"
            + " or aff_advertiser_manager_department_id in "
            + "    <foreach item='roleDeptId' index='index' collection='roleParam.advertiserManagerDepartmentIds' open='(' separator=',' close=')'>"
            + "       #{roleDeptId}"
            + "    </foreach> "
            + "</if>"
            + "<if test='roleParam.advertiserManagerId != null'>"
            + " or aff_advertiser_manager_id = #{roleParam.advertiserManagerId} "
            + "</if>"
            + ") "
            + "</if>"
            /* /权限 */
            + " group by sdate, symbol, affiliate_id"
            + "</script>")
    List<DailyDataRecordDO> affAll(@Param("param") AffAllParam param,
            @Param("roleParam") AffDataRoleParam roleParam, @Param("date") String sdate);

    @Select("<script>"
            + "select "
            + "sdate, "
            + "symbol, "
            + "affiliate_id companyId, "
            + "sum(clicks) clicks, "
            + "sum(unique_clicks) uniqueClicks, "
            + "sum(gross_conversions) grossConversions, "
            + "sum(revenue) revenue, "
            + "sum(cost) cost, "
            + "sum(conversions) conversions, "
            + "sum(sc) sc "
            + " from tb_data_record "
            + " where sdate between date_format(#{param.begin},'%Y-%m-%d') and date_format(#{param.end},'%Y-%m-%d') "
            + " and affiliate_id = #{param.companyId} "
            + "<if test='param.departmentIds != null'>"
            + " and aff_advertiser_manager_department_id in "
            + "    <foreach item='paramDeptId' index='index' collection='param.departmentIds' open='(' separator=',' close=')'>"
            + "       #{paramDeptId}"
            + "    </foreach> "
            + "</if>"
            + "<if test='param.advertiserManagerId != null'>"
            + " and aff_advertiser_manager_id = #{param.advertiserManagerId} "
            + "</if>"
            /* 权限 */
            + "<if test='roleParam != null'>"
            + " and (1=1 "
            + "<if test='roleParam.advertiserManagerDepartmentIds != null and roleParam.advertiserManagerDepartmentIds.size() > 0'>"
            + " or aff_advertiser_manager_department_id in "
            + "    <foreach item='roleDeptId' index='index' collection='roleParam.advertiserManagerDepartmentIds' open='(' separator=',' close=')'>"
            + "       #{roleDeptId}"
            + "    </foreach> "
            + "</if>"
            + "<if test='roleParam.advertiserManagerId != null'>"
            + " or aff_advertiser_manager_id = #{roleParam.advertiserManagerId} "
            + "</if>"
            + ") "
            + "</if>"
            /* /权限 */
            + " group by sdate, symbol, affiliate_id"
            + "</script>")
    List<DailyDataRecordDO> affAllGroupBySdateAndSymbol(@Param("param") AffAllGroupByDayAndSymbolParam param,
            @Param("roleParam") AffDataRoleParam roleParam);

    @Select("<script>select symbol, " +
            "<foreach item='item' index='index' collection='SELECT_COLUMNS' separator=','>" +
            "${item.columnName} ${item.alias} " +
            "</foreach>" +
            "from tb_data_record " +
            " where sdate between date_format(#{param.startDate},'%Y-%m-%d') and date_format(#{param.endDate},'%Y-%m-%d') and advertiser_id = #{param.companyId} " +
            "<choose>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0 and param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and (adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=')'> #{item}</foreach>" +
            " or adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=')'> #{item}" +
            "</foreach>" +
            ")</when>" +
            "<otherwise>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0'>" +
            " and adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "<when test='param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "</otherwise> " +
            "</choose>" +

            "<if test='param.advAdvertiserManagerDepartmentIdListSelected != null'>" +
            " and adv_advertiser_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAffiliateManagerDepartmentIdListSelected != null'>" +
            " and adv_affiliate_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAdvertiserManagerIdSelected != null'>" +
            "and adv_advertiser_manager_id = #{param.advAdvertiserManagerIdSelected}" +
            "</if>" +
            "<if test='param.advAffiliateManagerIdSelected != null'>" +
            "and adv_affiliate_manager_id = #{param.advAffiliateManagerIdSelected}" +
            "</if>" +

            "<if test='param.affIds != null and param.affIds.size()!=0' >" +
            "    and affiliate_id in " +
            "<foreach item='item' index='index' collection='param.affIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advIds != null and param.advIds.size()!=0' >" +
            "    and advertiser_id in " +
            "<foreach item='item' index='index' collection='param.advIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.customerIdList != null and param.customerIdList.size()!=0'>" +
            "and customer_id in " +
            "<foreach item='item' index='index' collection='param.customerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.offerIds != null and param.offerIds.size()!=0' >" +
            "    and offer_id in " +
            "<foreach item='item' index='index' collection='param.offerIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.affDepartmentIds != null and param.affDepartmentIds != 0'>" +
            "<foreach item='item' index='index' collection='param.affDepartmentIds' open=' (' separator=',' close=') '>" +
            "${item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.affAdvertiserManagerId != null and param.affAdvertiserManagerId != 0'>" +
            "aff_advertiser_manager_id = #{param.affAdvertiserManagerId}"+
            "</if>" +
            "<if test='param.accessType != null'>" +
            " and access_type = #{param.accessType} " +
            "</if>" +
            "group by sdate,symbol " +
            "<foreach item='item' index='index' collection='groupByColumns' separator=''>" +
            ", ${item}" +
            "</foreach>" +
            "order by sdate desc" +
            " limit ${param.fromLine},${param.rows} " +
            "</script>")
    List<MergeDataRecordDO> mergeDataRecord(@Param("param") ReportParam param,
            @Param("SELECT_COLUMNS") List<SelectColumnNameAndAlias> selectColumns,
            @Param("groupByColumns") List<String> groupByColumns,
            @Param("sZone") String sZone);

    @Select("<script>select count(1) from (" +
            " select 1" +
            " from tb_data_record " +
            " where sdate between date_format(#{param.startDate},'%Y-%m-%d') and date_format(#{param.endDate},'%Y-%m-%d') and advertiser_id = #{param.companyId} " +
            "<choose>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0 and param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and (adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=')'> #{item}</foreach>" +
            " or adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=')'> #{item}" +
            "</foreach>" +
            ")</when>" +
            "<otherwise>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0'>" +
            " and adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "<when test='param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "</otherwise> " +
            "</choose>" +


            "<if test='param.advAdvertiserManagerDepartmentIdListSelected != null'>" +
            " and adv_advertiser_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAffiliateManagerDepartmentIdListSelected != null'>" +
            " and adv_affiliate_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAdvertiserManagerIdSelected != null'>" +
            "and adv_advertiser_manager_id = #{param.advAdvertiserManagerIdSelected}" +
            "</if>" +
            "<if test='param.advAffiliateManagerIdSelected != null'>" +
            "and adv_affiliate_manager_id = #{param.advAffiliateManagerIdSelected}" +
            "</if>" +

            "<if test='param.affIds != null and param.affIds.size()!=0' >" +
            "    and affiliate_id in " +
            "<foreach item='item' index='index' collection='param.affIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advIds != null and param.advIds.size()!=0' >" +
            "    and advertiser_id in " +
            "<foreach item='item' index='index' collection='param.advIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.customerIdList != null and param.customerIdList.size()!=0'>" +
            "and customer_id in " +
            "<foreach item='item' index='index' collection='param.customerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.offerIds != null and param.offerIds.size()!=0' >" +
            "    and offer_id in " +
            "<foreach item='item' index='index' collection='param.offerIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.affAdvertiserManagerId != null and param.affAdvertiserManagerId != 0'>"+
            " and aff_advertiser_manager_id = #{param.affAdvertiserManagerId}"+
            "</if>"+
            "<if test='param.accessType != null'>" +
            " and access_type = #{param.accessType} " +
            "</if>" +
            " group by  " +
            "<foreach item='item' index='index' collection='groupByColumns' separator=','>" +
            " ${item}" +
            "</foreach>) tmp </script>")
    int countDataRecord(@Param("param") ReportParam param, @Param("groupByColumns") List<String> dataRecordGroupColumns, @Param("sZone") String sZone);

    @Select("<script>select " +
            "symbol,"+
            " sum(clicks) clicks, " +
            " sum(unique_clicks) uniqueClicks, " +
            " sum(gross_conversions) grossConversions, " +
            " sum(revenue) revenue, " +
            " sum(cost) cost, " +
            " sum(conversions) conversions, " +
            " sum(sc) scrubbedConversions " +
            " from tb_data_record " +
            " where sdate between date_format(#{param.startDate},'%Y-%m-%d') and date_format(#{param.endDate},'%Y-%m-%d') and advertiser_id = #{param.companyId} " +
            "<choose>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0 and param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and (adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=')'> #{item}</foreach>" +
            " or adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=')'> #{item}" +
            "</foreach>" +
            ")</when>" +
            "<otherwise>" +
            "<when test='param.advAffiliateManagerIdList != null and param.advAffiliateManagerIdList.size()!=0'>" +
            " and adv_affiliate_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "<when test='param.advAdvertiserManagerIdList != null and param.advAdvertiserManagerIdList.size()!=0'>" +
            " and adv_advertiser_manager_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</when>" +
            "</otherwise> " +
            "</choose>" +

            "<if test='param.advAdvertiserManagerDepartmentIdListSelected != null'>" +
            " and adv_advertiser_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAdvertiserManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAffiliateManagerDepartmentIdListSelected != null'>" +
            " and adv_affiliate_manager_department_id in " +
            "<foreach item='item' index='index' collection='param.advAffiliateManagerDepartmentIdListSelected' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advAdvertiserManagerIdSelected != null'>" +
            "and adv_advertiser_manager_id = #{param.advAdvertiserManagerIdSelected}" +
            "</if>" +
            "<if test='param.advAffiliateManagerIdSelected != null'>" +
            "and adv_affiliate_manager_id = #{param.advAffiliateManagerIdSelected}" +
            "</if>" +

            "<if test='param.affIds != null and param.affIds.size()!=0' >" +
            "    and affiliate_id in " +
            "<foreach item='item' index='index' collection='param.affIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.advIds != null and param.advIds.size()!=0' >" +
            "    and advertiser_id in " +
            "<foreach item='item' index='index' collection='param.advIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.customerIdList != null and param.customerIdList.size()!=0'>" +
            "and customer_id in " +
            "<foreach item='item' index='index' collection='param.customerIdList' open='(' separator=',' close=') '>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.offerIds != null and param.offerIds.size()!=0' >" +
            "    and offer_id in " +
            "<foreach item='item' index='index' collection='param.offerIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='param.affAdvertiserManagerId != null and param.affAdvertiserManagerId != 0'>"+
            "and aff_advertiser_manager_id = #{param.affAdvertiserManagerId}"+
            "</if>" +
            "<if test='param.accessType != null'>" +
            " and access_type = #{param.accessType} " +
            "</if>"+
            "GROUP BY symbol" +
            "</script>")
    List<MergeDataRecordDO> sumDataRecord(@Param("param") ReportParam param, @Param("sZone") String sZone);

    @Select("<script> select " +
            "   sdate as sdates," +
            "   symbol," +
            " sum(clicks) clicks, " +
            " sum(unique_clicks) uniqueClicks, " +
            " sum(gross_conversions) grossConversions, " +
            " sum(conversions) conversions, " +
            " sum(sc) scrubbedConversions, " +
            " sum(cost) cost, " +
            " sum(revenue) revenue " +
            " from tb_data_record " +
            " where sdate between date_format(#{begin},'%Y-%m-%d') and date_format(#{end},'%Y-%m-%d')" +
            "and advertiser_id = #{param.advertiserId}"+
            "<if test='param.hasAllPermission==false'>"+
            "  and (1>2 "+
            "<if test='param.advertiserManagerId != null'>"+
            "   or adv_advertiser_manager_id = #{param.advertiserManagerId} "+
            "</if>"+
            "<if test='param.affiliateManagerId != null'>"+
            "   or adv_affiliate_manager_id = #{param.affiliateManagerId} "+
            "</if>"+
            "<if test='param.advertiserManagerDepartmentIdSet != null and param.advertiserManagerDepartmentIdSet.size()!=0' >" +
            "    or adv_advertiser_manager_department_id in " +
            "    <foreach item='item' index='index' collection='param.advertiserManagerDepartmentIdSet' open='(' separator=',' close=')'>" +
            "       #{item}" +
            "    </foreach>" +
            "</if>" +
            "<if test='param.affiliateManagerDepartmentIdSet != null and param.affiliateManagerDepartmentIdSet.size()!=0' >" +
            "    or adv_affiliate_manager_department_id in " +
            "    <foreach item='item' index='index' collection='param.affiliateManagerDepartmentIdSet' open='(' separator=',' close=')'>" +
            "       #{item}" +
            "    </foreach>" +
            "</if>" +
            " )" +
            "</if> " +
            " group by sdate, symbol" +
            "   order by sdate desc" +
            "</script>"
    )
    List<MergeDataRecordDO> findByAdvIdAndPeriod(@Param("param") AdvertiserWholeDayDataForPeriodParamDTO param,
                                                                      @Param("begin") Integer begin,
                                                                      @Param("end") Integer end,
                                                                      @Param("sZone") String zone);


    enum AdvertiserMergeColumnAndAlias {
        Sdate(AdvertiserReportParamDTO.AdvertiserOfferReportField.Sdate, "sdate", "sdates"),
        Affiliate(AdvertiserReportParamDTO.AdvertiserOfferReportField.Affiliate, "affiliate_id", "affiliateId"),
        Offer(AdvertiserReportParamDTO.AdvertiserOfferReportField.Offer, "offer_id", "offerId"),
        AdvAffiliateManager(AdvertiserReportParamDTO.AdvertiserOfferReportField.AdvAffiliateManager, "adv_affiliate_manager_id", "advAffiliateManagerId"),
        AdvAdvertiserManager(AdvertiserReportParamDTO.AdvertiserOfferReportField.AdvAdvertiserManager, "adv_advertiser_manager_id", "advAdvertiserManagerId"),
        Customer(AdvertiserReportParamDTO.AdvertiserOfferReportField.Customer, "customer_id", "customerId"),
        Clicks(AdvertiserReportParamDTO.AdvertiserOfferReportField.Clicks, "sum(clicks)", "clicks"),
        UniqueClicks(AdvertiserReportParamDTO.AdvertiserOfferReportField.UniqueClicks, "sum(unique_clicks)", "uniqueClicks"),
        GrossConversions(AdvertiserReportParamDTO.AdvertiserOfferReportField.GrossConversions, "sum(gross_conversions)", "grossConversions"),
        Revenue(AdvertiserReportParamDTO.AdvertiserOfferReportField.Revenue, "sum(revenue)", "revenue"),
        Cost(AdvertiserReportParamDTO.AdvertiserOfferReportField.Cost, "sum(cost)", "cost"),
        Conversions(AdvertiserReportParamDTO.AdvertiserOfferReportField.Conversions, "sum(conversions)", "conversions"),
        ScrubbedConversions(AdvertiserReportParamDTO.AdvertiserOfferReportField.ScrubbedConversions, "sum(sc)", "scrubbedConversions"),
        AccessType(AdvertiserReportParamDTO.AdvertiserOfferReportField.AccessType, "access_type", "accessType");

        private final AdvertiserReportParamDTO.AdvertiserOfferReportField field;
        private final SelectColumnNameAndAlias selectColumnNameAndAlias;

        public static SelectColumnNameAndAlias select(OfferReportField field) {
            if (field == null || !field.isSelect()) {
                return null;
            }
            return Arrays.stream(AdvertiserMergeColumnAndAlias.values())
                    .filter(item -> item.field == field.getField())
                    .findFirst()
                    .map(item -> item.selectColumnNameAndAlias)
                    .orElse(null);
        }

        public static String groupBy(OfferReportField field) {
            return Arrays.stream(AdvertiserMergeColumnAndAlias.values())
                    .filter(item -> item.field != null && field.isGroupBy() && item.field == field.getField())
                    .findFirst()
                    .map(item -> item.selectColumnNameAndAlias.columnName)
                    .orElse(null);
        }

        AdvertiserMergeColumnAndAlias(AdvertiserReportParamDTO.AdvertiserOfferReportField field, String columnName, String alias) {
            this.field = field;
            this.selectColumnNameAndAlias = new SelectColumnNameAndAlias(columnName, alias);
        }

        public SelectColumnNameAndAlias getSelectColumnNameAndAlias() {
            return this.selectColumnNameAndAlias;
        }
    }

    enum AffiliateMergeColumnAndAlias {
        Sdate(AffiliateReportParamDTO.AffiliateOfferReportField.Sdate, "sdate", "sdates"),
        Advertiser(AffiliateReportParamDTO.AffiliateOfferReportField.Advertiser, "advertiser_id", "advertiserId"),
        AdvAffiliateManager(AffiliateReportParamDTO.AffiliateOfferReportField.AffAdvertiserManager, "aff_advertiser_manager_id", "affAdvertiserManagerId"),
        Offer(AffiliateReportParamDTO.AffiliateOfferReportField.Offer, "offer_id", "offerId"),
        AffiliateSource(AffiliateReportParamDTO.AffiliateOfferReportField.AffiliateSource, "sorce_id", "sourceId"),
        Sub1(AffiliateReportParamDTO.AffiliateOfferReportField.Sub1, "sub1", "sub1"),
        Sub2(AffiliateReportParamDTO.AffiliateOfferReportField.Sub2, "sub2", "sub2"),
        Sub3(AffiliateReportParamDTO.AffiliateOfferReportField.Sub3, "sub3", "sub3"),
        Sub4(AffiliateReportParamDTO.AffiliateOfferReportField.Sub4, "sub4", "sub4"),
        Sub5(AffiliateReportParamDTO.AffiliateOfferReportField.Sub5, "sub5", "sub5"),
        Clicks(AffiliateReportParamDTO.AffiliateOfferReportField.Clicks, "sum(clicks)", "clicks"),
        UniqueClicks(AffiliateReportParamDTO.AffiliateOfferReportField.UniqueClicks, "sum(unique_clicks)", "uniqueClicks"),
        GrossConversions(AffiliateReportParamDTO.AffiliateOfferReportField.GrossConversions, "sum(gross_conversions)", "grossConversions"),
        Revenue(AffiliateReportParamDTO.AffiliateOfferReportField.Revenue, "sum(revenue)", "revenue"),
        Cost(AffiliateReportParamDTO.AffiliateOfferReportField.Cost, "sum(cost)", "cost"),
        Conversions(AffiliateReportParamDTO.AffiliateOfferReportField.Conversions, "sum(conversions)", "conversions"),
        ScrubbedConversions(AffiliateReportParamDTO.AffiliateOfferReportField.ScrubbedConversions, "sum(sc)", "scrubbedConversions"),;
        private final AffiliateReportParamDTO.AffiliateOfferReportField field;
        private final SelectColumnNameAndAlias selectColumnNameAndAlias;

        public static SelectColumnNameAndAlias select(OfferReportField field) {
            if (field == null || !field.isSelect()) {
                return null;
            }
            return Arrays.stream(AffiliateMergeColumnAndAlias.values())
                    .filter(item -> item.field == field.getField())
                    .findFirst()
                    .map(item -> item.selectColumnNameAndAlias)
                    .orElse(null);
        }

        public static String groupBy(OfferReportField field) {
            if (field == null) {
                return null;
            }
            return Arrays.stream(AffiliateMergeColumnAndAlias.values())
                    .filter(item -> item.field != null && field.isGroupBy() && item.field == field.getField())
                    .findFirst()
                    .map(item -> item.selectColumnNameAndAlias.columnName)
                    .orElse(null);
        }

        AffiliateMergeColumnAndAlias(AffiliateReportParamDTO.AffiliateOfferReportField field, String columnName, String alias) {
            this.field = field;
            this.selectColumnNameAndAlias = new SelectColumnNameAndAlias(columnName, alias);
        }

        public SelectColumnNameAndAlias getSelectColumnNameAndAlias() {
            return this.selectColumnNameAndAlias;
        }
    }
}
